package com.hctforgreen.greenservice.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 数据库帮助类
 * 开机密码表、消息、视频剧集等表
 * @author ld 20131122
 * 
 */
public class DBHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "greenpwd.db";
	/**
	 * /10:更新反馈历史记录表增加附件文件路径
	 */
	
    //数据库版本，如果数据库表有新增字段，则要改数据库版本
	private static final int DATABASE_VERSION = 12;

	private static final String TABLE_PWD = "table_pwd";
	private static final String TABLE_TEMP_PWD = "table_temp_pwd";

	private static final String TABLE_MESSAGE = "table_message";

	private static final String TABLE_FEEDBACK_INFO = "table_feedback_info";
	private static final String TABLE_FEEDBACK_IMG = "table_feedback_img";

	private static final String TABLE_VIDEO_PART = "table_video_part";

	private static final String TABLE_VIDEO_DOWNLOAD = "table_video_download";

	private static final String TABLE_BASE_DATA = "table_base_data";// 基础数据（信息资料中的版块及系列）
	/**
	 * 创建开机密码
	 */
	private String CREATE_TABLE_PWD = "CREATE TABLE IF NOT EXISTS table_pwd"
			+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ "stripeCode VARCHAR, "
			+ "openPassword VARCHAR, "
			+ "projectName VARCHAR, "
			+ "ownerName VARCHAR, "
			+ "ownerPhone VARCHAR,"
			+ "note TEXT, "
			+ "date VARCHAR, "
			+ "time VARCHAR,"
			+ "province VARCHAR,"
			+ "city VARCHAR,"
			+ "district VARCHAR,"
			+ "street VARCHAR,"
			+ "industry TEXT,"
			+ "sparebootPwd VARCHAR DEFAULT '',"
			+ "identityCode VARCHAR  DEFAULT '',"
			+ "verifyStatus VARCHAR DEFAULT '',"
			+ "refuseReason VARCHAR  DEFAULT '')"; // DATABASE_VERSION
	// =
	// 5时add
	/**
	 * 消息
	 */
	private String CRETATE_TABLE_MESSAGE = "CREATE TABLE IF NOT EXISTS "
			+ TABLE_MESSAGE + "(id VARCHAR PRIMARY KEY ," + "date VARCHAR,"
			+ "seriesName VARCHAR," + "readStatus INTEGER,"
			+ "recentMsg INTEGER," + "msgTitle VARCHAR)";

	/**
	 * 视频剧集
	 */
	private String CREATE_TABLE_VIDEOPART = "CREATE TABLE IF NOT EXISTS "
			+ TABLE_VIDEO_PART + "(id VARCHAR PRIMARY KEY ,"
			+ "picUrl VARCHAR," + "videoUrl VARCHAR," + "videoName VARCHAR,"
			+ "part VARCHAR)";
	/**
	 * 视频下载
	 */
	private String CREATE_TABLE_VIDEO_DOWNLOAD = "CREATE TABLE IF NOT EXISTS "
			+ TABLE_VIDEO_DOWNLOAD + "(id VARCHAR PRIMARY KEY ,"
			+ "picUrl VARCHAR," + "videoUrl VARCHAR," + "videoName VARCHAR,"
			+ "state VARCHAR," + "fileSize VARCHAR," + "part VARCHAR)";

	/**
	 * 创建反馈信息
	 */
	private String CRETATE_TABLE_FEEDBACK_INFO = "CREATE TABLE IF NOT EXISTS "
			+ TABLE_FEEDBACK_INFO + "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ "date VARCHAR," + "seriesName VARCHAR," + "content VARCHAR,"
			+ "videoPath VARCHAR," + "filePathes VARCHAR," + "title VARCHAR,"
			+ "uploadFilePath VARCHAR)";

	/**
	 * DATABASE_VERSION = 5时add
	 */
	private static final String CREATE_TEMP_PWD_TABLE_SQL = "alter table "
			+ TABLE_PWD + " rename to " + TABLE_TEMP_PWD;

	private static final String INSERT_TEMP_PWD_TABLE_DATA_2_PWD_TABLE_SQL = "insert into "
			+ TABLE_PWD + " select *,''"// ''是为新增的字段插入空值
			+ " from " + TABLE_TEMP_PWD;

	private String DROP_TEMP_PWD_TABLE_SQL = "DROP TABLE IF EXISTS "
			+ TABLE_TEMP_PWD;

	public DBHelper(Context context) {
		// CursorFactory设置为null,使用默认值
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// 数据库第一次被创建时onCreate会被调用 历史机会
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE_PWD);
		db.execSQL(CRETATE_TABLE_MESSAGE);
		db.execSQL(CRETATE_TABLE_FEEDBACK_INFO);
		db.execSQL(CREATE_TABLE_VIDEOPART);// 观看历史记录表
		db.execSQL(CREATE_TABLE_VIDEO_DOWNLOAD);// 视频下载表
		// db.execSQL(CRETATE_TABLE_FEEDBACK_IMG);
	}

	// 如果DATABASE_VERSION值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion == 4 && newVersion == 5) {
			db.execSQL(CREATE_TEMP_PWD_TABLE_SQL);
			db.execSQL(CREATE_TABLE_PWD);
			db.execSQL(CREATE_TABLE_VIDEOPART);
			db.execSQL(INSERT_TEMP_PWD_TABLE_DATA_2_PWD_TABLE_SQL);
			db.execSQL(DROP_TEMP_PWD_TABLE_SQL);
			return;
		}
		 // db.execSQL("DROP TABLE IF EXISTS " + TABLE_PWD);

		if (oldVersion == 9 && newVersion >= 10) {
			db.execSQL("ALTER TABLE " + TABLE_FEEDBACK_INFO
					+ " ADD uploadFilePath VARCHAR");
		} else if (oldVersion == 10 && newVersion >= 11) {
			/*
			 * 增加备用密码字段 wuhs20150717
			 */
			db.execSQL("ALTER TABLE " + TABLE_PWD
					+ " ADD sparebootPwd VARCHAR  DEFAULT '' ");
		} else if (oldVersion == 11 && newVersion >= 12) {
			/*
			 * 增加身份码字段
			 */
			db.execSQL("ALTER TABLE " + TABLE_PWD
					+ " ADD identityCode VARCHAR  DEFAULT '' ");
			//新增大机组审核状态字段---390694
			db.execSQL("ALTER TABLE " + TABLE_PWD
					+ " ADD verifyStatus VARCHAR DEFAULT '' ");
			//新增大机组字段---390694
			db.execSQL("ALTER TABLE " + TABLE_PWD
					+ " ADD refuseReason VARCHAR  DEFAULT '' ");

		}else {//跨版本升级，直接删除表，重建
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGE);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_FEEDBACK_INFO);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_PWD);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_VIDEO_PART);
		}

		onCreate(db);
	}
}
